﻿Imports Entidad
Imports System.Data
Imports System.Data.SqlClient
Public Class RutaDatos

    Function listarPedidosRutas() As DataSet
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()
        Dim query As String = "SELECT dc.DCI_Codigo AS Pedido, c.CLI_Codigo, c.CLI_Descripcion AS Cliente, e.EST_Abreviatura AS Estado, " &
                              "(CASE WHEN dc.AGE_Codigo IS NULL THEN ISNULL(d.DIS_Descripcion,'') ELSE ISNULL(di.DIS_Descripcion,'') END) AS Distrito, " &
                              "ISNULL(tabla.Cant1,'0') AS CantBultos, ISNULL(tabla1.ItemsLargos,0) AS CantLargos, " &
                              "ISNULL(tabla.Cant2,'0.0') AS PesoTotal, '0.0' AS Volumen, r.RUT_Descripcion, r.RUT_Codigo " &
                              "FROM Documento_Cliente dc " &
                              "LEFT JOIN Cliente c " &
                              "ON c.CLI_Codigo = dc.CLI_Codigo AND c.CLI_Activo = '1' " &
                              "LEFT JOIN ClienteEntrega ce " &
                              "ON ce.CLI_Codigo = c.CLI_Codigo AND ce.CLEN_Activo = '1' " &
                              "LEFT JOIN Distrito d " &
                              "ON d.DIS_Codigo = ce.DIS_Codigo AND d.DIS_Activo = '1' " &
                              "INNER JOIN Estado e " &
                              "ON e.EST_Codigo = dc.DCI_Estado AND e.EST_Almacen = '1' " &
                              "LEFT JOIN (SELECT dc.DCI_Codigo, COUNT(dcb.DCI_Codigo) AS Cant1, SUM(dcb.DCB_PesoBruto) AS Cant2 " &
                              "FROM Documento_Cliente dc " &
                              "INNER JOIN Documento_Cliente_Bulto dcb " &
                              "ON dcb.CIA = dc.CIA AND dcb.SUC_Codigo = dc.SUC_Codigo AND dcb.TDOC_Codigo = dc.TDOC_Codigo " &
                              "AND dcb.DCI_Codigo = dc.DCI_Codigo AND dcb.DCB_Activo = '1' " &
                              "GROUP BY dc.DCI_Codigo) AS tabla " &
                              "ON tabla.DCI_Codigo = dc.DCI_Codigo " &
                              "LEFT JOIN Documento_Cliente_Detalle dcd " &
                              "ON dcd.CIA = dc.CIA AND dcd.SUC = dc.SUC_Codigo AND dcd.TDOC_Codigo = dc.TDOC_Codigo " &
                              "AND dcd.DCI_Codigo = dc.DCI_Codigo AND dcd.DCD_Activo = '1' " &
                              "LEFT JOIN (SELECT dcad.CIA_Cliente, dcad.SUC_Cliente, dcad.TDOC_Cliente, dcad.DCI_Codigo, COUNT(*) AS ItemsLargos " &
                              "FROM Documento_Cliente_Almacen_Detalle dcad " &
                              "INNER JOIN VW_VistaDocumentoAlmacen da " &
                              "ON da.CIA = dcad.CIA_Almacen AND da.SUC_Codigo = dcad.SUC_Almacen AND da.TDOC_Codigo = dcad.TDOC_Almacen " &
                              "AND da.DCA_Codigo = dcad.DCA_Codigo AND da.DAD_Codigo = dcad.DAD_Codigo AND da.BAN_Codigo = '00000' " &
                              "GROUP BY dcad.CIA_Cliente, dcad.SUC_Cliente, dcad.TDOC_Cliente, dcad.DCI_Codigo) tabla1 " &
                              "ON tabla1.CIA_Cliente = dc.CIA AND tabla1.SUC_Cliente = dc.SUC_Codigo AND tabla1.TDOC_Cliente = dc.TDOC_Codigo " &
                              "AND tabla1.DCI_Codigo = dc.DCI_Codigo " &
                              "LEFT JOIN Ruta r " &
                              "ON r.RUT_Codigo = dc.RUT_Codigo " &
                              "LEFT JOIN AgenciaTransporteSucursal ats " &
                              "ON ats.AGE_Codigo = dc.AGE_Codigo AND ats.ATS_Codigo = dc.ATS_Codigo AND ats.ATS_Activo = '1' " &
                              "LEFT JOIN Distrito di " &
                              "ON di.DIS_Codigo = ats.DIS_Codigo AND di.DIS_Activo = '1' " &
                              "GROUP BY dc.DCI_Codigo, c.CLI_Descripcion,d.DIS_Descripcion, e.EST_Abreviatura,r.RUT_Descripcion, " &
                              "c.CLI_Codigo,r.RUT_Codigo,tabla.Cant1,tabla.Cant2,dc.AGE_Codigo,di.DIS_Descripcion,tabla1.ItemsLargos"

        Dim adapter As New SqlDataAdapter(query, coneccion)
        Dim ds As New DataSet
        adapter.Fill(ds)

        coneccion.Close()

        Return ds
    End Function

    Function listarPedidosXRuta(codRuta As String) As DataSet
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()
        Dim query As String = "SELECT dc.DCI_Codigo AS Pedido, COUNT(dcb.DCI_Codigo) AS CantBultos, " &
                              "ISNULL(tabla1.ItemsLargos,0) AS CantLargos, " &
                              "SUM(ISNULL(dcb.DCB_PesoBruto,0)) AS PesoTotal, '0.0' AS Volumen, e.EST_Abreviatura " &
                              "FROM Ruta r " &
                              "INNER JOIN Documento_Cliente dc " &
                              "ON dc.RUT_Codigo = r.RUT_Codigo AND dc.DCI_Programado = '0' " &
                              "INNER JOIN Cliente c " &
                              "ON dc.CLI_Codigo = c.CLI_Codigo AND c.CLI_Activo = '1' " &
                              "LEFT JOIN ClienteEntrega ce ON ce.CLI_Codigo = c.CLI_Codigo AND ce.CLEN_Activo = '1' " &
                              "LEFT JOIN Distrito d ON d.DIS_Codigo = ce.DIS_Codigo AND d.DIS_Activo = '1' " &
                              "INNER JOIN Estado e ON e.EST_Codigo = dc.DCI_Estado AND e.EST_Almacen = '1' " &
                              "LEFT JOIN Documento_Cliente_Bulto dcb ON dcb.CIA = dc.CIA AND dcb.SUC_Codigo = dc.SUC_Codigo " &
                              "AND dcb.TDOC_Codigo = dc.TDOC_Codigo AND dcb.DCI_Codigo = dc.DCI_Codigo AND dcb.DCB_Activo = '1' " &
                              "LEFT JOIN (SELECT dc.DCI_Codigo,COUNT(*) AS Cantidad " &
                              "FROM Documento_Cliente dc " &
                              "INNER JOIN Documento_Cliente_Detalle dcd " &
                              "ON dc.DCI_Codigo = dcd.DCI_Codigo AND dcd.DCD_Activo = '1' " &
                              "GROUP BY dc.DCI_Codigo) AS tabla " &
                              "ON tabla.DCI_Codigo = dc.DCI_Codigo " &
                              "LEFT JOIN (SELECT dcad.CIA_Cliente, dcad.SUC_Cliente, dcad.TDOC_Cliente, dcad.DCI_Codigo, COUNT(*) AS ItemsLargos " &
                              "FROM Documento_Cliente_Almacen_Detalle dcad " &
                              "INNER JOIN VW_VistaDocumentoAlmacen da " &
                              "ON da.CIA = dcad.CIA_Almacen AND da.SUC_Codigo = dcad.SUC_Almacen AND da.TDOC_Codigo = dcad.TDOC_Almacen " &
                              "AND da.DCA_Codigo = dcad.DCA_Codigo AND da.DAD_Codigo = dcad.DAD_Codigo AND da.BAN_Codigo = '00000' " &
                              "GROUP BY dcad.CIA_Cliente, dcad.SUC_Cliente, dcad.TDOC_Cliente, dcad.DCI_Codigo) tabla1 " &
                              "ON tabla1.CIA_Cliente = dc.CIA AND tabla1.SUC_Cliente = dc.SUC_Codigo AND tabla1.TDOC_Cliente = dc.TDOC_Codigo " &
                              "AND tabla1.DCI_Codigo = dc.DCI_Codigo " &
                              "WHERE r.RUT_Codigo = '" & codRuta & "' " &
                              "GROUP BY dc.DCI_Codigo, e.EST_Abreviatura,tabla1.ItemsLargos"

        Dim adapter As New SqlDataAdapter(query, coneccion)
        Dim ds As New DataSet
        adapter.Fill(ds)

        coneccion.Close()

        Return ds
    End Function

    'Function listarRutas() As DataSet
    '    Dim conex As New Conexion
    '    Dim coneccion As SqlConnection = conex.conectar()
    '    coneccion.Open()
    '    Dim query As String = "SELECT r.RUT_Descripcion AS Rutas, COUNT(dcb.DCI_Codigo) AS CantBultos, " &
    '                          "ISNULL(SUM(dcb.DCB_PesoBruto),0) AS PesoTotal, '0.00' AS Volumen " &
    '                          "FROM Ruta r " &
    '                          "LEFT JOIN Cliente c " &
    '                          "ON c.RUT_Codigo = r.RUT_Codigo AND c.CLI_Activo = '1' " &
    '                          "INNER JOIN Documento_Cliente dc " &
    '                          "ON dc.CLI_Codigo = c.CLI_Codigo " &
    '                          "INNER JOIN Estado e " &
    '                          "ON e.EST_Codigo = dc.DCI_Estado AND e.EST_Almacen = '1' " &
    '                          "LEFT JOIN Documento_Cliente_Bulto dcb " &
    '                          "ON dcb.CIA = dc.CIA AND dcb.SUC_Codigo = dc.SUC_Codigo " &
    '                          "AND dcb.TDOC_Codigo = dc.TDOC_Codigo AND dcb.DCI_Codigo = dc.DCI_Codigo " &
    '                          "AND dcb.DCB_Activo = '1' " &
    '                          "GROUP BY r.RUT_Descripcion"

    '    Dim adapter As New SqlDataAdapter(query, coneccion)
    '    Dim ds As New DataSet
    '    adapter.Fill(ds)

    '    coneccion.Close()

    '    Return ds
    'End Function

    Function listarRutas() As DataSet
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()
        Dim query As String = "SELECT r.RUT_Codigo, r.RUT_Descripcion, '0' AS N1, '0' AS N2, '0.00' AS N3, '0.00' AS N4, " &
                              "COUNT(dc.DCI_Codigo) AS CantPed " &
                              "FROM Ruta r " &
                              "LEFT JOIN Documento_Cliente dc " &
                              "ON dc.RUT_Codigo = r.RUT_Codigo AND dc.DCI_Estado IN ('09','14') " &
                              "GROUP BY r.RUT_Codigo, r.RUT_Descripcion " &
                              "ORDER BY r.RUT_Codigo"

        Dim adapter As New SqlDataAdapter(query, coneccion)
        Dim ds As New DataSet
        adapter.Fill(ds)

        coneccion.Close()

        Return ds
    End Function

    Sub actualizarRutaCliente(codpedido As String, codRuta As String)
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()
        Dim query As String = "UPDATE Documento_Cliente " &
                              "SET RUT_Codigo = @Codigo " &
                              "WHERE DCI_Codigo = @codPedido"

        Dim comando As New SqlCommand
        With comando
            .Connection = coneccion
            .CommandText = query
            .Parameters.AddWithValue("@codPedido", codpedido)
            .Parameters.AddWithValue("@Codigo", codRuta)
            .ExecuteNonQuery()
        End With

        coneccion.Close()
    End Sub

    Function obtenerDatosCliente(codigoPedido As String) As DataSet
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()
        Dim query As String = "SELECT ce.CLEN_Direccion, at.AGE_Descripcion, ats.ATS_Direccion, d.DIS_Descripcion AS DAgencia, di.DIS_Descripcion AS DCliente " &
                              "FROM Documento_Cliente dc " &
                              "LEFT JOIN AgenciaTransporte at " &
                              "ON at.AGE_Codigo = dc.AGE_Codigo AND at.AGE_Activo = '1' " &
                              "LEFT JOIN AgenciaTransporteSucursal ats " &
                              "ON ats.AGE_Codigo = dc.AGE_Codigo AND ats.ATS_Codigo = dc.ATS_Codigo AND ats.ATS_Activo = '1' " &
                              "LEFT JOIN Distrito d " &
                              "ON d.DIS_Codigo = ats.DIS_Codigo AND d.DIS_Activo = '1' " &
                              "LEFT JOIN ClienteEntrega ce " &
                              "ON ce.CLEN_Codigo = dc.CLEN_Codigo AND ce.CLI_Codigo = dc.CLI_Codigo AND ce.CLEN_Activo = '1' " &
                              "LEFT JOIN Distrito di " &
                              "ON di.DIS_Codigo = ce.DIS_Codigo AND di.DIS_Activo = '1' " &
                              "WHERE dc.DCI_Codigo = '" & codigoPedido & "'"

        Dim adapter As New SqlDataAdapter(query, coneccion)
        Dim ds As New DataSet
        adapter.Fill(ds)

        coneccion.Close()

        Return ds
    End Function

    Function obtenerRuta(codRuta As String) As String
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()
        Dim query As String = "SELECT RUT_Descripcion " &
                              "FROM Ruta " &
                              "WHERE RUT_Codigo = @COD"

        Dim comando As New SqlCommand(query, coneccion)
        With comando
            .Parameters.AddWithValue("@COD", codRuta)
        End With
        Dim adapter As New SqlDataAdapter(comando)
        Dim ds As New DataSet
        adapter.Fill(ds)

        coneccion.Close()

        Return ds.Tables(0).Rows(0)("RUT_Descripcion").ToString.Trim
    End Function

End Class
